<%@ page import="java.io.File"%>
<%@ page import="java.util.Date"%>
<h1>My Applications</h1>
<a href="dashboard.jsp#pending">Pending Scholarships</a><br/>
<a href="dashboard.jsp#awarded">Awarded Scholarships</a><br/>
<a href="dashboard.jsp#denied">Denied Scholarships</a><br/>
<%
	// Get current logged in user.
	String user = (String) session.getAttribute("user");

	// Getting information and updating the database.
	java.sql.Connection conn = null;
	try {
		String myJDBCDriver = (String) session.getAttribute("myJDBCDriver");
		String myURL = (String) session.getAttribute("myURL");
		String myUserID = (String) session.getAttribute("myUserID");
		String myPassword = (String) session.getAttribute("myPassword");

		// Connect to the database.
		Class.forName(myJDBCDriver).newInstance();
		java.util.Properties sysprops = System.getProperties();
		sysprops.put("user", myUserID);
		sysprops.put("password", myPassword);
		conn = java.sql.DriverManager.getConnection(myURL, sysprops);

		java.sql.Statement statement = conn.createStatement();
		java.sql.ResultSet rs = statement
				.executeQuery("SELECT scholarships.*, won FROM scholarships JOIN applications ON scholarships.id=scholarship_id "
						+ "WHERE applicant='" + user + "'" + " ORDER BY deadline DESC");
		java.sql.ResultSetMetaData columns = rs.getMetaData();
		out.println("<table><tr><td>");
		// Current scholarships.
		out.println("<a id=\"pending\"></a>");
		out.println("<h2>Pending Review</h2>");
		while (rs.next() && rs.getDate("deadline").getTime() > (new Date()).getTime()) {
			out.println("<table id=\"scholarship\">");
			for (int i = 2; i <= columns.getColumnCount() - 3; i++) {
				out.println("<tr>");
				out.println("<th>" + columns.getColumnName(i) + ":</th>");
				out.println("<td>" + rs.getString(i) + "</td>");
				out.println("</tr>");
			}
			String docPath = application.getRealPath("scholarship_documents") + "\\" + rs.getString("id") + ".doc";
			File file = new File(docPath);
			if (file.exists()) {
				out.println("<tr><th>extra_info</th><td><a href=\"" + "scholarship_documents/" + file.getName()
						+ "\">Download Extra Information</a></td></tr>");
			}
			out.println("</table>");
			out.println("<br />");
		}
		// Awarded scholarships.
		out.println("<a id=\"awarded\"></a>");
		out.println("<h2>Awarded Scholarships</h2>");
		rs.beforeFirst();
		while (rs.next()) {
			if (rs.getDate("deadline").getTime() < (new Date()).getTime() && rs.getInt("won") == 1) {
				out.println("<table id=\"scholarship\">");
				for (int i = 2; i <= columns.getColumnCount() - 3; i++) {
					out.println("<tr>");
					out.println("<th>" + columns.getColumnName(i) + ":</th>");
					out.println("<td>" + rs.getString(i) + "</td>");
					out.println("</tr>");
				}
				String docPath = application.getRealPath("scholarship_documents") + "\\" + rs.getString("id") + ".doc";
				File file = new File(docPath);
				if (file.exists()) {
					out.println("<tr><th>extra_info</th><td><a href=\"" + "scholarship_documents/" + file.getName()
							+ "\">Download Extra Information</a></td></tr>");
				}
				out.println("</table>");
				out.println("<br />");
			}
		}
		// Denied scholarships.
		out.println("<a id=\"denied\"></a>");
		out.println("<h2>Denied Scholarships</h2>");
		rs.beforeFirst();
		while (rs.next()) {
			if (rs.getDate("deadline").getTime() < (new Date()).getTime() && rs.getInt("won") == 0) {
				out.println("<table id=\"scholarship\">");
				for (int i = 2; i <= columns.getColumnCount() - 3; i++) {
					out.println("<tr>");
					out.println("<th>" + columns.getColumnName(i) + ":</th>");
					out.println("<td>" + rs.getString(i) + "</td>");
					out.println("</tr>");
				}
				String docPath = application.getRealPath("scholarship_documents") + "\\" + rs.getString("id") + ".doc";
				File file = new File(docPath);
				if (file.exists()) {
					out.println("<tr><th>extra_info</th><td><a href=\"" + "scholarship_documents/" + file.getName()
							+ "\">Download Extra Information</a></td></tr>");
				}
				out.println("</table>");
				out.println("<br />");
			}
		}

		out.println("</td></tr></table");
	} catch (Exception e) {
		e.printStackTrace();
		out.print(e.toString());
	} finally {
		try {
			conn.close();
		} catch (Exception ee) {
		}
	}
%>